package com.jms.fu.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import com.jms.fu.exception.DAOException;
import com.jms.fu.model.bean.App;
import com.jms.fu.model.bean.Comment;

public class AppDAO {
	// Init variable
	Connection conn = null;
	PreparedStatement pStm = null;
	Statement stm = null;
	ResultSet rs = null;

	public void updateApp(App app) throws DAOException {
		String sql = "UPDATE tb_App SET appName = ? , price = ? , description = ? , "
				+ "upVersion = ? , requireOS = ? , size = ? WHERE appId = ?";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, app.getAppName());
			pStm.setFloat(2, app.getPrice());
			pStm.setString(3, app.getDescription());
			pStm.setString(4, app.getUpVersion());
			pStm.setString(5, app.getRequireOS());
			pStm.setString(6, app.getSize());
			pStm.setInt(7, app.getAppId());
			pStm.executeUpdate();
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}

	public void updateFullApp(App app) throws DAOException {
		String sql = "UPDATE tb_App SET appName = ? , price = ? , description = ? , "
				+ "upVersion = ? , requireOS = ? , size = ? , numberOfDownload = ? WHERE appId = ?";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, app.getAppName());
			pStm.setFloat(2, app.getPrice());
			pStm.setString(3, app.getDescription());
			pStm.setString(4, app.getUpVersion());
			pStm.setString(5, app.getRequireOS());
			pStm.setString(6, app.getSize());
			pStm.setInt(8, app.getAppId());
			pStm.setInt(7, app.getNumberOfDownload());
			pStm.executeUpdate();
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}
	
	public void deleteApp(int appId) throws DAOException {
		String sql = "DELETE FROM tb_App WHERE appId = ?";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setInt(1, appId);
			pStm.executeUpdate();
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public boolean checkPath(String path) throws DAOException {
		String sql = "SELECT appid FROM tb_App WHERE link = ?";

		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, path);
			rs = pStm.executeQuery();
			if (rs.next()) {
				return false;
			}
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

		return true;
	}

	public void uploadApp(App app) throws DAOException {
		String sql = "INSERT INTO tb_App(appName,price,numberOfDownload,upVersion,rate,"
				+ "link,description,dateUpload,category,devId,requireOS,size) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, app.getAppName());
			pStm.setFloat(2, app.getPrice());
			pStm.setInt(3, app.getNumberOfDownload());
			pStm.setString(4, app.getUpVersion());
			pStm.setFloat(5, app.getRate());
			pStm.setString(6, app.getLink());
			pStm.setString(7, app.getDescription());
			Date date = new Date();
			pStm.setDate(8, new java.sql.Date(date.getTime()));
			pStm.setString(9, app.getCategory());
			pStm.setString(10, app.getDevId());
			pStm.setString(11, app.getRequireOS());
			pStm.setString(12, app.getSize());
			pStm.executeUpdate();
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}
	public void addComment(int appId,String username,String comment) throws DAOException {
		String sql = "INSERT INTO tb_Comment(appId,userId,con_tent,dateComment) VALUES (?,?,?,?)";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setInt(1, appId);
			pStm.setString(2, username);
			pStm.setString(3, comment);
			pStm.setDate(4, new java.sql.Date(new Date().getTime()));
			pStm.executeUpdate();
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}
	public ArrayList<Comment> getCommentByAppId(int appId) throws DAOException {
		String sql = "SELECT appId,userId,con_tent,dateComment FROM tb_Comment WHERE appId = ?";
		ArrayList<Comment> listComment = new ArrayList<Comment>();
		Comment temp = new Comment();
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setInt(1, appId);
			rs = pStm.executeQuery();
			while (rs.next()) {
				temp = new Comment();
				temp.setAppId(rs.getInt(1));
				temp.setUserId(rs.getString(2));
				temp.setCon_tent(rs.getString(3));
				temp.setDateComment(rs.getDate(4));
				listComment.add(temp);
			}
			return listComment;
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}

	public App getAppByID(int appid) throws DAOException {
		String sql = "SELECT appId,appName,price,numberOfDownload,upVersion,rate,link,description,dateUpload,requireOS,size,devId FROM tb_App WHERE appId = ?";
		App app = new App();
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setInt(1, appid);
			rs = pStm.executeQuery();
			if (rs.next()) {
				app.setAppId(rs.getInt(1));
				app.setAppName(rs.getString(2));
				app.setPrice(rs.getFloat(3));
				app.setNumberOfDownload(rs.getInt(4));
				app.setUpVersion(rs.getString(5));
				app.setRate(rs.getFloat(6));
				app.setLink(rs.getString(7));
				app.setDescription(rs.getString(8));
				app.setDateUpload(rs.getDate(9));
				app.setRequireOS(rs.getString(10));
				app.setSize(rs.getString(11));
				app.setDevId(rs.getString(12));
			}
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return app;
	}

	public int getNumberOfPageApp(String cat, String pricefilter,
			int numberinpage) throws DAOException {

		String priceFilter = "";
		if (pricefilter.equals("free")) {
			priceFilter = "price = 0";
		}
		if (pricefilter.equals("notfree")) {
			priceFilter = "price > 0";
		}
		String catFilter = "";
		if (!priceFilter.equals("")) {
			catFilter = " AND";
		}

		if (!"all".equals(cat.toLowerCase())) {
			catFilter += " category = '" + cat + "'";
		} else {
			catFilter = "";
		}

		String filterquery = "";
		if (!catFilter.equals("") || !priceFilter.equals("")) {
			filterquery = "WHERE " + priceFilter + catFilter;
		}

		String sql = "SELECT COUNT(appId) FROM tb_App " + filterquery;
		System.out.println("------" + sql);
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			rs = pStm.executeQuery();
			int totalpage = 0;
			if (rs.next()) {
				totalpage = rs.getInt(1);
			}
			if (totalpage % numberinpage == 0) {
				return totalpage / numberinpage;
			}
			return totalpage / numberinpage + 1;
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}

	}

	public ArrayList<App> getListApp(String cat, String sortby,
			String pricefilter, int numberinpage, int pagenumber)
			throws DAOException {
		// generate sql query to get list app base on user request
		String priceFilter = "";
		if (pricefilter.equals("free")) {
			priceFilter = "price = 0";
		}
		if (pricefilter.equals("notfree")) {
			priceFilter = "price > 0";
		}
		String catFilter = "";
		if (!priceFilter.equals("")) {
			catFilter = " AND";
		}

		if (!"all".equals(cat.toLowerCase())) {
			catFilter += " category = '" + cat + "'";
		} else {
			catFilter = "";
		}

		String filterquery = "";
		if (!catFilter.equals("") || !priceFilter.equals("")) {
			filterquery = "WHERE " + priceFilter + catFilter;
		}

		int belowLimit = (pagenumber - 1) * numberinpage + 1;
		int upperLimit = pagenumber * numberinpage;

		String sql = "SELECT appId,appName,price,numberOfDownload,upVersion,rate,link,devId "
				+ "FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY "
				+ sortby +("appname".equals(sortby)?"":" DESC")
				+ " ) AS RowNum,appId,appname,price,"
				+ "numberOfDownload,upVersion,rate,link,devId FROM tb_App "
				+ filterquery
				+ ") "
				+ "AS RowConstrainedResult "
				+ "WHERE RowNum >= ? " + "AND RowNum <= ? " + "ORDER BY RowNum";

		System.out.println("------" + sql);
		ArrayList<App> listApp = new ArrayList<App>();
		App temp;
		// Create connection to excute query and get information
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setInt(1, belowLimit);
			pStm.setInt(2, upperLimit);
			rs = pStm.executeQuery();
			while (rs.next()) {
				temp = new App();
				temp.setAppId(rs.getInt(1));
				temp.setAppName(rs.getString(2));
				temp.setPrice(rs.getFloat(3));
				temp.setNumberOfDownload(rs.getInt(4));
				temp.setUpVersion(rs.getString(5));
				temp.setRate(rs.getFloat(6));
				temp.setLink(rs.getString(7));
				temp.setDevId(rs.getString(8));
				listApp.add(temp);
			}
			return listApp;

		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}
	}

	public ArrayList<App> getHotApp() throws DAOException {
		ArrayList<App> listApp = new ArrayList<App>();
		String sql =  "SELECT TOP 8 appId,appName,price,numberOfDownload,upVersion,rate,link,description,dateUpload,requireOS,size,devId FROM tb_App ORDER BY numberOfDownload DESC";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			rs = pStm.executeQuery();
			
			while(rs.next()){
				App app = new App();
				app.setAppId(rs.getInt(1));
				app.setAppName(rs.getString(2));
				app.setPrice(rs.getFloat(3));
				app.setNumberOfDownload(rs.getInt(4));
				app.setUpVersion(rs.getString(5));
				app.setRate(rs.getFloat(6));
				app.setLink(rs.getString(7));
				app.setDescription(rs.getString(8));
				app.setDateUpload(rs.getDate(9));
				app.setRequireOS(rs.getString(10));
				app.setSize(rs.getString(11));
				app.setDevId(rs.getString(12));
				listApp.add(app);
			}
			return listApp;
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}
	}
	
	public ArrayList<App> getAppByUser(String username) throws DAOException {
		ArrayList<App> listApp = new ArrayList<App>();
		String sql =  "SELECT appId,appName,price,numberOfDownload,upVersion,rate,link,description,dateUpload,requireOS,size,devId FROM tb_App WHERE devId = ?";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			rs = pStm.executeQuery();
			
			while(rs.next()){
				App app = new App();
				app.setAppId(rs.getInt(1));
				app.setAppName(rs.getString(2));
				app.setPrice(rs.getFloat(3));
				app.setNumberOfDownload(rs.getInt(4));
				app.setUpVersion(rs.getString(5));
				app.setRate(rs.getFloat(6));
				app.setLink(rs.getString(7));
				app.setDescription(rs.getString(8));
				app.setDateUpload(rs.getDate(9));
				app.setRequireOS(rs.getString(10));
				app.setSize(rs.getString(11));
				app.setDevId(rs.getString(12));
				listApp.add(app);
			}
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}
		
		return listApp;
	}
	
	public ArrayList<App> getAppDownByUser(String username) throws DAOException {
		ArrayList<App> listApp = new ArrayList<App>();
		ArrayList<Integer> temp = new ArrayList<>();
		App tempApp;
		String sql =  "SELECT appId FROM tb_AppDown WHERE userID = ?";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			rs = pStm.executeQuery();
			
			while(rs.next()){
				temp.add(rs.getInt(1));
			}
			int k= temp.size();
			for (int i = 0; i < k; i++) {
				tempApp = new App();
				tempApp = getAppByID(temp.get(i));
				System.out.println(temp.get(i));
				listApp.add(tempApp);
			}
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}
		
		return listApp;
	}
	
	public ArrayList<App> getAppBySearch(String search) throws DAOException {
		ArrayList<App> listApp = new ArrayList<App>();
		String sql =  "SELECT appId,appName,price,numberOfDownload,upVersion,rate,link,description,dateUpload,requireOS,size,devId FROM tb_App WHERE appName LIKE '%"+search+"%'";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			rs = pStm.executeQuery();
			
			while(rs.next()){
				App app = new App();
				app.setAppId(rs.getInt(1));
				app.setAppName(rs.getString(2));
				app.setPrice(rs.getFloat(3));
				app.setNumberOfDownload(rs.getInt(4));
				app.setUpVersion(rs.getString(5));
				app.setRate(rs.getFloat(6));
				app.setLink(rs.getString(7));
				app.setDescription(rs.getString(8));
				app.setDateUpload(rs.getDate(9));
				app.setRequireOS(rs.getString(10));
				app.setSize(rs.getString(11));
				app.setDevId(rs.getString(12));
				listApp.add(app);
			}
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}
		
		return listApp;
	}

	public ArrayList<App> getNewApp() throws DAOException {
		ArrayList<App> listApp = new ArrayList<App>();
		String sql =  "SELECT TOP 5 appId,appName,price,numberOfDownload,upVersion,rate,link,description,dateUpload,requireOS,size,devId FROM tb_App ORDER BY dateUpload DESC";
		try {
			conn = ConnectionFactory.getConnection();
			pStm = conn.prepareStatement(sql);
			rs = pStm.executeQuery();
			
			while(rs.next()){
				App app = new App();
				app.setAppId(rs.getInt(1));
				app.setAppName(rs.getString(2));
				app.setPrice(rs.getFloat(3));
				app.setNumberOfDownload(rs.getInt(4));
				app.setUpVersion(rs.getString(5));
				app.setRate(rs.getFloat(6));
				app.setLink(rs.getString(7));
				app.setDescription(rs.getString(8));
				app.setDateUpload(rs.getDate(9));
				app.setRequireOS(rs.getString(10));
				app.setSize(rs.getString(11));
				app.setDevId(rs.getString(12));
				listApp.add(app);
			}
			return listApp;
		} catch (DAOException | SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}
	}
	
}
